Schema definition topic

For package:typed_sql to offer a convenient type-safe API for writing SQL queries we need to define the database schema and generate code from it.

Database Schema

The schema must be defined in a single Dart library, this is typically called model.dart, the generated part will be written to model.g.dart using build_runner. Thus, we must always include this part-file as follows:

import 'package:typed_sql/typed_sql.dart';

part 'model.g.dart';

We must then define a schema class. This is an abstract final class extending Schema, specifying what tables the database has. If we were working on a bookstore we might define a schema as follows:

abstract final class Bookstore extends Schema {
  Table<Author> get authors;
  Table<Book> get books;
}

The schema class is only allowed to have abstract getters that returns Table objects. Each such getter defines a table in the database. The T in Table<T> must be a row class specifying the table layout.

Note

Each table must have its own row class. It's not possible to reuse the same row class for multiple tables.

Row class for `Author`

For each table in our database we must define a row class. This is an abstract final class extending the Row class, specifying what fields the database table has. Continuing with the bookstore example we can define a row class for the authors table as follows:

@PrimaryKey(['authorId'])
abstract final class Author extends Row {
  @AutoIncrement()
  int get authorId;

  @Unique()
  String get name;
}

The Author row class specifies a table with two fields:

authorId, and,
name.

The authorId will be a 64 bit integer, auto-incremented by default and used as primary key. The name field will be TEXT and have two constraints NOT NULL (because the Dart getter isn't nullable) and UNIQUE because of the @Unique() annotation.

The equivalent SQL depends on the database, but it looks something like:

CREATE TABLE authors (
  authorId INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  UNIQUE (name)
);

The following data types are allowed for fields:

bool,
int,
double,
String,
DateTime,
Uint8List, and,
Custom subclasses of CustomDataType<T>.

These types are allowed to be nullable and non-nullable. When fields are non-nullable in the Dart row class the SQL table will have a NOT NULL constraint. For details on CustomDataType see Custom data types documentation.

Note

These row classes are intended to be data classes, they may not have constructors or members other than abstract public getters. Nor can they subclass or implement other classes.

If you want to add custom helper methods, you may write extension methods for your row classes.

`Book` row class with foreign key

Returning to our bookstore example, we still need to define a Book row class for the books table in the Bookstore schema. If we want the books table to have a foreign key referencing the authors table we can define the Book row class as follows:

@PrimaryKey(['bookId'])
abstract final class Book extends Row {
  @AutoIncrement()
  int get bookId;

  String? get title;

  @References(
    // This fields references "authorId" from "authors" table
    table: 'authors',
    field: 'authorId',

    // The reference is _named_ "author", this gives rise to a
    // Expr<Book>.author property when building queries.
    name: 'author', // optional

    // This is referenced _as_ "books", this gives rise to a
    // Expr<Author>.books property when building queries.
    as: 'books', // optional
  )
  int get authorId;

  @DefaultValue(0)
  int get stock;
}

The @DefautValue(0) annotation gives the stock field a default value of 0. This also makes the stock field optional when inserting rows.

Note

The name and as properties in the @References annotation are optional. These gives rise to convinient subquery properties we can use when writing queries later.

The equivalent SQL depends on the database, but it looks something like:

CREATE TABLE books (
  bookId BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  title TEXT,
  authorId BIGINT NOT NULL,
  stock BIGINT NOT NULL DEFAULT 0,
  FOREIGN KEY (authorId) REFERENCES authors (authorId)
);

Notice that because the title field is nullable, it does not have a NOT NULL constraint in the database.

Generating code

Whenever the definition of the database schema is changed, it's important to run code generation. This is done using build_runner. If you don't have a dev-dependency on build_runner you may add this as follows:

dart pub add dev:build_runnner

You can run code-generation by invoking build_runner as follows:

dart run build_runner build

This should create or update the model.g.dart part-file for your model.dart library.

Using the database

When we've defined a schema and generated code we can start using the database. We must first create a Database instance using a DatabaseAdapter and SqlDialect as follows:

final db = Database<Bookstore>(adapter, dialect);

How you obtain a DatabaseAdapter and which SqlDialect to use depends on your database, as well as how you are connecting.

Once you have Database<Bookstore> instance you can create empty tables for your schema as follows:

// Create tables
await db.createTables();

Creating empty tables from scratch is mostly useful for testing, it's rarely needed in production. Instead you can use the generated create<Schema>Tables, which outputs the DDL for creating the tables.

// Get the database schema
final ddl = createBookstoreTables(SqlDialect.postgres());

This can then be used with external migration management tools to managing database migrations. See Migrations documentation.

Note

The astute reader might notice that we never actually create an instance of Bookstore schema class. Instead the class is used to define a type we can parameterize the Database instance with. This is intentional to avoid juggling constructors, but it might be revised in the future.

With a Database<Bookstore> and tables created through migrations or db.createTables() you can insert data into the database as follows:

// Insert a row into the "authors" table
final author = await db.authors
    .insert(
      name: toExpr('Easter Bunny'),
    )
    .returnInserted()
    .executeAndFetch(); // returns Future<Author?>

// Insert a row into the "books" table
await db.books
    .insert(
      title: toExpr('How to hide eggs'),
      authorId: toExpr(author.authorId),
    )
    .execute();

Now we can also write queries against the database. The following demonstrates how to write a query that filters on the book title and only returns title and author.name.

// Query for books where the title contains 'eggs'
// select the title and author name
final titleAndAuthor = await db.books
    .where(
      (book) => book.title
          .orElseValue('') // because title can be null
          .toLowerCase()
          .containsValue('eggs'),
    )
    .select(
      (book) => (
        book.title,
        book.author.name, // use the 'author' subquery property
      ),
    )
    .fetch();

// Compare the results
check(titleAndAuthor).unorderedEquals([
  ('How to hide eggs', 'Easter Bunny'),
]);

The astute reader might notice that author.name is not actually a property on the books table. However, because we defined a foreign key on Book row class with the @References annotation, and gave it the name author, the Expr<Book> expression gets a subquery property book.author that allows us to access properties on the referenced authors row.

See References documentation for details.

Schema definition topic

Database Schema

Row class for `Author`

`Book` row class with foreign key

Generating code

Using the database

Classes

typed_sql package

Schema definition topic

Schema definition topic

Database Schema

Row class for Author

Book row class with foreign key

Generating code

Using the database

Classes

typed_sql package

Schema definition topic

Row class for `Author`

`Book` row class with foreign key